﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ClassLibrary.Entity;
using ClassLibrary.Data;
using System.Data;

namespace ClassLibrary.DAO
{
    public class TblCustomersDAO
    {
        private DBService _db = null;
        public TblCustomersDAO(string ConnectionString)
        {
            _db = new DBService(ConnectionString);
        }
        public bool InsUpdCustomers(TblCustomers customer, int mode)
        {
            bool flag = false;
            try
            {
                _db.ExeSP_NonQuery("sp_TblCustomer_InsUpd",
                    new string[] {"@CustomerIndex", "@CustomerTitle", "@CustomerFirstname", "@CustomerLastname", "@CustomerFullname", "@Companyname", 
                                  "@postalnumber", "@postalstreet", "@postalsuburb", "@postalpostcode", "@postalstate", "@phonework", "@phonehome",
	                              "@phonemobile", "@phonefacsimile", "@emailaddress", "@CustomerUsername", "@emailpassword", "@PreferContactMethod", "@DateCreated", "@mode" },
                    new object[] {customer.CustomerIndex, customer.CustomerTitle, customer.CustomerFirstname, customer.CustomerLastname, customer.CustomerFullname, customer.Companyname,
                                  customer.Postalnumber, customer.Postalstreet, customer.Postalsuburb, customer.Postalpostcode, customer.Postalstate, customer.Phonework, customer.Phonehome, 
                                  customer.Phonemobile, customer.Phonefacsimile, customer.Emailaddress, customer.CustomerUsername, customer.Emailpassword, customer.PreferContactMethod, customer.DateCreated, mode},
                    CommandType.StoredProcedure);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }

        public DataTable GetState()
        {
            DataTable dt = null;
            try
            {
                dt = _db.ExeSP_DataTable("select * from AUState", false, CommandType.Text);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        public bool CheckPostCode(string state, string postcode)
        {
            bool flag = false;
            try
            {
                DataTable dt = _db.ExeSP_DataTable("select * from " +
                     "(select b.*  from  AUSuburb b, AUState where AUState.state=b.state and b.state= @state)A  where postcode= @postcode",
                     new string[] { "@state", "@postcode" },
                     new object[] { state, postcode },
                     CommandType.Text);
                if (dt.Rows.Count > 0)
                {
                    flag = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }

        public DataTable GetSuburb(string state, string postcode)
        {
            DataTable dt = null;
            try
            {
                dt = _db.ExeSP_DataTable("select * from " +
                     "(select b.*  from  AUSuburb b, AUState where AUState.state=b.state and b.state= @state)A  where postcode= @postcode",
                     new string[] { "@state", "@postcode" },
                     new object[] { state, postcode },
                     CommandType.Text);
               
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        public TblCustomers GetCustomer(string email, string pass)
        {
            TblCustomers customer = new TblCustomers();
            try
            {
                DataTable dt = _db.ExeSP_DataTable("CheckCustomer",
                    new string[] { "@email", "@pass" },
                    new object[] { email, pass}, CommandType.StoredProcedure);
                foreach (DataRow dr in dt.Rows)
                {
                    customer.CustomerIndex =Convert.ToInt32(dr["CustomerIndex"]);
                    customer.CustomerFirstname = dr["CustomerFirstname"].ToString();
                    customer.CustomerLastname = dr["CustomerLastname"].ToString();
                   // customer.DateCreated = Convert.ToDateTime(dr["DateCreated"].ToString());
                    customer.Emailaddress = dr["Emailaddress"].ToString();
                    //customer.CustomerIndex = Convert.ToInt32(dr["CustomerIndex"].ToString());
                    customer.Phonemobile = dr["Phonemobile"].ToString();
                    customer.CustomerFullname = dr["CustomerFullname"].ToString();
                    customer.Emailpassword = pass;

                }
            }
            catch ( Exception ex)
            {
                throw ex;
            }
            return customer;
        }

        public DataTable GetCustomerList(int CustomerId, string FirsName, string LastName, string mobilephone, string Email)
        {
            
            try
            {   
                return _db.ExeSP_DataTable("sp_CustomerList",
                    new string[] { "@CustomerID", "@FirstName", "@LastName", "@Phone", "@Email" },
                    new object[] { CustomerId, FirsName, LastName, mobilephone, Email },
                    CommandType.StoredProcedure);
            }
            catch (Exception ex)
            {
                throw ex;
            }
           
        }
        public DataTable GetCustomerId(int CustomerId)
        {
            try {
                return _db.ExeSP_DataTable("sp_GetCustomer",
                    new string[] { "@CustomerID" },
                    new object[] { CustomerId }, CommandType.StoredProcedure);
            }
            catch (Exception ex)
            { throw ex;
            }
        }
        public bool CheckEmail(string Email, int Mode)
        {
            bool flag = false;
            try
            { 
            DataTable dt =_db.ExeSP_DataTable("sp_CheckEmail",
                new string [] {"@Email", "@Mode"},
                new object [] { Email, Mode}, CommandType.StoredProcedure);

                    if (dt.Rows.Count > 0)
                        flag = true;
            }
            catch ( Exception ex)
            {
                throw ex;         
            }
            return flag;
        }
    }
}
